﻿-- 创建用户表
CREATE TABLE jb_user (
  user_id                   guid            NOT NULL,
  username                  varchar(18)     NOT NULL,
  `password`                varchar(24)     NOT NULL,
  reg_date                  datetime        NOT NULL,
  lastedlogin_date          datetime        NULL,
  -- Constraints
  CONSTRAINT pk_usr_id PRIMARY KEY(user_id),
  CONSTRAINT uq_usr_usrnm UNIQUE(username)
);
GO

-- 创建用户信息表
CREATE TABLE jb_userdetail (
	detail_id                 guid            NOT NULL,
	gender                    bit             NULL,
	constellation             byte            NULL,
	`description`             varchar(140)    NULL,
	-- Constraints
	CONSTRAINT pk_usrdtl_id PRIMARY KEY(detail_id),
	CONSTRAINT fk_dtl_usr_id FOREIGN KEY(detail_id) REFERENCES jb_user(user_id)
	  ON DELETE CASCADE ON UPDATE CASCADE
	);
GO

-- 创建用户关系表
CREATE TABLE jb_relationship (
  rlnshp_id                 autoincrement   NOT NULL,
  mineuser_id               guid            NOT NULL,
  yoursuser_id              guid            NOT NULL,
  -- Constraints
  CONSTRAINT pk_rlnshp_id PRIMARY KEY(rlnshp_id),
  CONSTRAINT fk_rlnshp_usr_mineid FOREIGN KEY(mineuser_id) REFERENCES jb_user(user_id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_rlnshp_usr_yoursid FOREIGN KEY(yoursuser_id) REFERENCES jb_user(user_id)
    ON DELETE CASCADE ON UPDATE CASCADE
);
GO

-- 创建用户关系表索引
CREATE INDEX idx_rlnshp_usr_mineid ON jb_relationship(mineuser_id ASC);
GO
CREATE INDEX idx_rlnshp_usr_yoursid ON jb_relationship(yoursuser_id ASC);
GO

-- 创建信息表
CREATE TABLE jb_message (
  msg_id                    guid            NOT NULL,
  owneruser_id              guid            NOT NULL,
  messagetext               varchar(140)    NOT NULL,
  send_date                 datetime        NOT NULL,
  sawcount                  long            NOT NULL DEFAULT 1,
  -- Constraints
  CONSTRAINT pk_msg_id PRIMARY KEY(msg_id),
  CONSTRAINT fk_msg_usr_id FOREIGN KEY(owneruser_id) REFERENCES jb_user(user_id)
    ON DELETE CASCADE ON UPDATE CASCADE
);
GO

-- 创建信息表索引
CREATE INDEX idx_msg_usr_id ON jb_message(owneruser_id);
GO

-- 创建喜欢表
 CREATE TABLE jb_likeit (
   like_id                  guid            NOT NULL,
   msg_id                   guid            NOT NULL,
   likeuser_id              guid            NULL,
   -- Constraints
   CONSTRAINT pk_like_id PRIMARY KEY(like_id),
   CONSTRAINT fk_likeit_msg_id FOREIGN KEY(msg_id) REFERENCES jb_message (msg_id)
     ON DELETE SET NULL ON UPDATE CASCADE,
   CONSTRAINT fk_likeit_usr_id FOREIGN KEY(likeuser_id) REFERENCES jb_user (user_id)
     ON DELETE SET NULL ON UPDATE CASCADE
);
GO

-- 创建喜欢表索引
CREATE INDEX idx_likeit_msg_id ON jb_likeit(msg_id ASC);
GO
CREATE INDEX idx_likeit_usr_id ON jb_likeit(likeuser_id ASC);
GO

-- 创建评论表
CREATE TABLE jb_comment (
  comment_id                guid            NOT NULL,
  msg_id                    guid            NULL,
  owneruser_id              guid            NOT NULL,
  atuser_id                 guid            NULL,
  commenttext               varchar(140)    NOT NULL,
  -- Constraints
  CONSTRAINT pk_cmt_id PRIMARY KEY(comment_id),
  CONSTRAINT fk_cmt_msg_id FOREIGN KEY(msg_id) REFERENCES jb_message(msg_id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_cmt_wnrusr_id FOREIGN KEY(owneruser_id) REFERENCES jb_user(user_id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_cmt_atusr_id FOREIGN KEY(atuser_id) REFERENCES jb_user(user_id)
    ON DELETE SET NULL ON UPDATE CASCADE
);
GO

-- 创建评论表索引
CREATE INDEX idx_cmt_wnrusr_id ON jb_comment(owneruser_id ASC);
GO
CREATE INDEX idx_cmt_atusr_id ON jb_comment(atuser_id ASC);
GO
